package com.seeyon.apps.ebs.rcyh.dao.impl;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.seeyon.apps.ebs.rcyh.dao.RcyhDao;
import com.seeyon.apps.ebs.rcyh.po.EbsRcMxPo;
import com.seeyon.apps.ebs.rcyh.po.EbsRcPo;
import com.seeyon.ctp.util.DBAgent;
import com.seeyon.ctp.util.FlipInfo;
import com.seeyon.ctp.util.JDBCAgent;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.util.*;

public class RcyhDaoImpl implements RcyhDao {
    private static Log log = LogFactory.getLog(RcyhDaoImpl.class);

    @Override
    public List<EbsRcMxPo> getRcMx(Long zId, Integer state) {
        List<EbsRcMxPo> ebsRcMxPos = null;
        try {
            String hql = "from "+EbsRcMxPo.class.getSimpleName()+" where zId=:zId and state=:state order by dqDate";
            Map<String,Object> param = new HashMap<>();
            param.put("zId",zId);
            param.put("state",state);
            ebsRcMxPos = DBAgent.find(hql, param);
        }catch (Exception e){
            log.error("getRcMxById异常！！！",e);
        }
        return ebsRcMxPos;
    }

    @Override
    public List<EbsRcMxPo> getRcMxById(Long id) {
        List<EbsRcMxPo> ebsRcMxPos = null;
        try {
            String hql = "from "+EbsRcMxPo.class.getSimpleName()+" where id=:id";
            Map<String,Object> param = new HashMap<>();
            param.put("id",id);
            ebsRcMxPos = DBAgent.find(hql, param);
        }catch (Exception e){
            log.error("getRcMxById异常！！！",e);
        }
        return ebsRcMxPos;
    }

    @Override
    public List<Map<String,Object>> getRcMxList(Map<String,Object> param) {
        List<Map<String,Object>> ebsRcMxPos = new ArrayList<>();
        JDBCAgent jdbc = null;
        try {
            String sql = "select * from ebs_rc_mx where member_id=:memberId and dq_date=:now";
            jdbc = new JDBCAgent(false, false);
            jdbc.executeNamedSql(sql,param);
            ebsRcMxPos = jdbc.resultSetToList();
        }catch (Exception e){
            log.error("getRcMxList异常！！！",e);
        }finally {
        jdbc.close();
    }
        return ebsRcMxPos;
    }

    @Override
    public void addRcMx(EbsRcMxPo rcMxPo) throws Exception {
        DBAgent.save(rcMxPo);
    }

    @Override
    public void updateRcMx(EbsRcMxPo rcMxPo) throws Exception {
        DBAgent.update(rcMxPo);
    }

    @Override
    public List<Map<String,Object>> getRcMx(Map<String,Object> param){
        List<Map<String,Object>> result = new ArrayList<>();
        JDBCAgent jdbc = null;
        try {
            String sql = "select * from ebs_rc_mx where zid=:zid and dq_date=:dq_date order by dq_date";
            jdbc = new JDBCAgent(false, false);
            jdbc.executeNamedSql(sql,param);
            result = jdbc.resultSetToList();
        }catch (Exception e){
            log.error("getRcMxList异常！！！",e);
        }finally {
            jdbc.close();
        }
        return result;
    }

    @Override
    public void addRc(EbsRcPo rcPo) throws Exception{
        DBAgent.save(rcPo);
    }

    @Override
    public void updateRc(EbsRcPo rcPo) throws Exception{
        DBAgent.update(rcPo);
    }

    @Override
    public List<EbsRcPo> getRc(EbsRcPo rcPo) throws Exception {
        String hql = "from "+EbsRcPo.class.getSimpleName()+" where memberId=:memberId and departmentId=:departmentId and year=:year and month=:month";
        Map<String,Object> param = new HashMap<>();
        param.put("memberId",rcPo.getMemberId());
        param.put("departmentId",rcPo.getDepartmentId());
        param.put("year",rcPo.getYear());
        param.put("month",rcPo.getMonth());
        List<EbsRcPo> ebsRcPos = DBAgent.find(hql,param);
        return ebsRcPos;
    }

    @Override
    public List<EbsRcPo> getRcList(JSONObject param) throws Exception{
        String hql = "from "+EbsRcPo.class.getSimpleName()+" where memberId=:memberId and year=:year";
        Map<String,Object> dataMap = new HashMap<>();
        Integer month = param.getInteger("month");
        if (month!=null){
            hql+=" and month=:month";
            dataMap.put("month",month);
        }
        hql+=" order by month";

        dataMap.put("memberId",param.getLong("memberId"));
        dataMap.put("year",param.getInteger("year"));
        List<EbsRcPo> ebsRcPos = DBAgent.find(hql,dataMap);
        return ebsRcPos;
    }

    @Override
    public List<EbsRcPo> getRcById(Long id) {
        List<EbsRcPo> ebsRcPos = null;
        try {
            String hql = "from "+EbsRcPo.class.getSimpleName()+" where id=:id";
            Map<String,Object> param = new HashMap<>();
            param.put("id",id);
            ebsRcPos = DBAgent.find(hql, param);
        }catch (Exception e){
            log.error("getRcById异常！！！",e);
        }
        return ebsRcPos;
    }

    @Override
    public FlipInfo getDpetDqMermbers(FlipInfo fi,JSONObject param) {
        List<Map<String, Object>> result = new ArrayList<>();
        JDBCAgent jdbc = null;
        try {
            String sql = "";
            Map<String,Object> dataMap = new HashMap<>();
            if (param.get("state")!=null && param.get("dq_date")!=null) {//根据当天动迁状态查询人员
                sql = "select rc.member_id from ebs_rc as rc LEFT JOIN ebs_rc_mx as mx on rc.id = mx.zid where";
                // sql+=" rc.year=:year";
                // sql+=" and rc.month=:month";
                sql+=" mx.org_department_id in (:deptIds)";
                sql+=" and mx.state=:state";
                sql+=" and mx.dq_date=:dq_date";
                if (param.get("name")!=null && !"".equals(param.get("name"))){
                    sql+=" and rc.name like concat('%',:name,'%')";
                    dataMap.put("name",param.get("name"));
                }
                sql+=" group by rc.member_id order by rc.zg_days desc";
                dataMap.put("state",param.get("state"));
                dataMap.put("dq_date",param.get("dq_date").toString());
            }else {//查询当月有动迁数据的所有人员
                sql += "select member_id from ebs_rc where year=:year and month=:month and org_department_id in (:deptIds)";
                if (param.get("name")!=null && !"".equals(param.get("name"))){
                    sql+=" and name like concat('%',:name,'%')";
                    dataMap.put("name",param.get("name"));
                }
                sql +=" group by member_id order by zg_days desc";
            }
            dataMap.put("year", param.getInteger("year"));
            dataMap.put("month", param.getInteger("month"));
            dataMap.put("deptIds", param.getJSONArray("deptIds"));
            jdbc = new JDBCAgent(false, false);
            jdbc.executeNamedSql(sql,dataMap);
            List<Map<String,Object>> list = jdbc.resultSetToList();
            if (list!=null&& !list.isEmpty()){
                JSONArray memberIds = new JSONArray();
                for (Map<String, Object> map : list) {
                    memberIds.add(map.get("member_id"));
                }
                JSONObject pxJson = param.getJSONObject("pxJson");
                if (pxJson!=null){
                    String pxSql = "select member_id from ebs_rc where member_id in (:memberIds) and year=:year and month=:pxmonth";
                    String sortType = pxJson.getString("sortType");
                    if ("1".equals(sortType)){//降序
                        pxSql+=" group by member_id order by zg_days desc";
                    }else if ("2".equals(sortType)){//升序
                        pxSql+=" group by member_id order by zg_days asc";
                    }
                    Map<String,Object> pxDataMap = new HashMap<>();
                    pxDataMap.put("memberIds",memberIds);
                    pxDataMap.put("year", param.getInteger("year"));
                    pxDataMap.put("pxmonth",pxJson.getString("sortMonth"));
                    jdbc.findNameByPaging(pxSql,pxDataMap,fi);
                }else{
                    jdbc.findNameByPaging(sql,dataMap,fi);
                }
            }
        }catch (Exception e){
            log.error("getDqMermbers异常！！！",e);
        }finally {
            jdbc.close();
        }
        return fi;
    }

    @Override
    public FlipInfo getDqMermbers(FlipInfo fi, JSONObject params) {
        List<Map<String, Object>> result = new ArrayList<>();
        JDBCAgent jdbc = null;
        try {
            String sql = "";
            Map<String, Object> paramMap = new HashMap<>();
            if (params.get("state")!=null && params.get("dq_date")!=null){//根据当天动迁状态查询人员
                sql = "select rc.member_id from ebs_rc as rc LEFT JOIN ebs_rc_mx as mx on rc.id = mx.zid where";
                sql+=" mx.state=:state";
                sql+=" and mx.dq_date=:dq_date";
                if (params.get("name")!=null && !"".equals(params.get("name"))){
                    sql+=" and rc.name like concat('%',:name,'%')";
                    paramMap.put("name",params.get("name"));
                }
                sql+=" group by rc.member_id order by rc.zg_days desc";
                paramMap.put("state",params.get("state"));
                paramMap.put("dq_date",params.get("dq_date").toString());
            }else{//查询当月有动迁数据的所有人员
                sql = "select rc.member_id from ebs_rc as rc where";
                sql+=" rc.month =:month";
                if (params.get("deptId")!=null && !"".equals(params.get("deptId"))){
                    sql+=" and rc.org_department_id =:deptId";
                    paramMap.put("deptId",params.get("deptId"));
                }
                if (params.get("name")!=null && !"".equals(params.get("name"))){
                    sql+=" and rc.name like concat('%',:name,'%')";
                    paramMap.put("name",params.get("name"));
                }
                sql+=" group by member_id order by zg_days desc";
                paramMap.put("month",params.get("month"));
            }
            jdbc = new JDBCAgent(false, false);
            jdbc.executeNamedSql(sql,paramMap);
            List<Map<String, Object>> list = jdbc.resultSetToList();
            if (list!=null&& !list.isEmpty()){
                JSONArray memberIds = new JSONArray();
                for (Map<String, Object> map : list) {
                    memberIds.add(map.get("member_id"));
                }
                JSONObject pxJson = params.getJSONObject("pxJson");
                if (pxJson!=null){
                    String pxSql = "select member_id from ebs_rc where member_id in (:memberIds) and year=:year and month=:pxmonth";
                    String sortType = pxJson.getString("sortType");
                    if ("1".equals(sortType)){//降序
                        pxSql+=" group by member_id order by zg_days desc";
                    }else if ("2".equals(sortType)){//升序
                        pxSql+=" group by member_id order by zg_days asc";
                    }
                    Map<String,Object> pxDataMap = new HashMap<>();
                    pxDataMap.put("memberIds",memberIds);
                    pxDataMap.put("year", params.getInteger("year"));
                    pxDataMap.put("pxmonth",pxJson.getString("sortMonth"));
                    jdbc.findNameByPaging(pxSql,pxDataMap,fi);
                }else{
                    jdbc.findNameByPaging(sql,paramMap,fi);
                }
            }
            log.info("查询所有动迁部门信息成功");
        }catch (Exception e){
            log.error("查询所有动迁部门信息失败",e);
        }finally {
            jdbc.close();
        }
        return fi;
    }
}
